﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace dzs.Web.supply
{
    public partial class AddSupply : System.Web.UI.Page
    {
        Maticsoft.BLL.supplyPlan sm;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindDDL();
                Bind();
            }

        }


        private void Bind()
        {
            sm = new Maticsoft.BLL.supplyPlan();

            string str = " innums < num ";

            if (this.ddl_projectTypeSel.SelectedValue != "-1")
            {
                str += " and projectTypeId= " + this.ddl_projectTypeSel.SelectedValue;
            }

            if (!string.IsNullOrEmpty(this.txt_importDateStart.Text))
            {
                str += " and importTime >='" + this.txt_importDateStart.Text + "' ";
            }
            if (!string.IsNullOrEmpty(this.txt_importDateEnd.Text))
            {
                str += " and importTime <='" + this.txt_importDateEnd.Text + "' ";
            }
            if (!string.IsNullOrEmpty(this.txt_projectName.Text))
            {
                str += " and projectName like '%"+this.txt_projectName.Text+"%' ";
            }
            if (!string.IsNullOrEmpty(this.txt_supplier.Text))
            {
                str += " and sname like '%" + this.txt_supplier.Text + "%' ";
            }

            DataSet ds = sm.GetList(str);

            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();

            this.lbl_count.Text = "共有"+ds.Tables[0].Rows.Count+"条数据";

        }
        private void BindDDL()
        {
            
            Maticsoft.BLL.projectType ptm = new Maticsoft.BLL.projectType();

            DataSet ds_ptype = ptm.GetList("");

            this.ddl_projectType.DataSource = ds_ptype;
            this.ddl_projectType.DataTextField = "projectTypeName";
            this.ddl_projectType.DataValueField = "projectTypeId";
            this.ddl_projectType.DataBind();

            this.ddl_projectType.Items.Insert(0, new ListItem("---未选择---", "-1"));

            this.ddl_projectTypeSel.DataSource = ds_ptype;
            this.ddl_projectTypeSel.DataTextField = "projectTypeName";
            this.ddl_projectTypeSel.DataValueField = "projectTypeId";
            this.ddl_projectTypeSel.DataBind();

            this.ddl_projectTypeSel.Items.Insert(0, new ListItem("---未选择---", "-1"));

            dzs.BLL.user um = new BLL.user();

            //DataSet ds = um.GetList("");

            DataSet ds = um.GetUserRoles(" roles_name='供应管理员' ");

            this.ddl_gygly.DataSource = ds;
            this.ddl_gygly.DataTextField = "user_name";
            this.ddl_gygly.DataValueField = "user_id";
            this.ddl_gygly.DataBind();

            this.ddl_gygly.Items.Insert(0, new ListItem("---未选择---", "-1"));
        }


        //分配供应管理员
        protected void btn_sendGly_Click(object sender, EventArgs e)
        {
            sm = new Maticsoft.BLL.supplyPlan ();
            if (this.ddl_gygly.SelectedValue == "-1")
            {
                Maticsoft.Common.MessageBox.Show(this,"请选择供应管理员");
            }
            else
            {
                for (int i = 0; i < this.GridView1.Rows.Count; i++)
                {
                    CheckBox cbo = this.GridView1.Rows[i].FindControl("cbo_check") as CheckBox;

                    if (cbo != null && cbo.Checked)
                    {
                        Label lbl = this.GridView1.Rows[i].FindControl("lbl_supplyPlanId") as Label;

                        if (!string.IsNullOrEmpty(lbl.Text))
                        {
                            string planId = lbl.Text;

                            Maticsoft.Model.supplyPlan s = sm.GetModel(int.Parse(planId));

                            s.user_id = int.Parse(this.ddl_gygly.SelectedValue);

                            sm.Update(s);
                        }
                    }
                }

                Bind();
                Maticsoft.Common.MessageBox.Show(this, "分配成功！");
       
            }
           
        }
     

        //导入excel
        protected void btn_importData_Click(object sender, EventArgs e)
        {
            string filename = string.Empty;
            //if (this.ddl_projectType.SelectedValue == "-1")
            //{
            //    Maticsoft.Common.MessageBox.Show(this,"请选择项目类型！");
            //    return;
            //}
            try
            {
                filename = UpLoadXls(FileExcel);//上传XLS文件
                string msg = ImportXlsToData(filename);//将XLS文件的数据导入数据库               
                if (filename != string.Empty && System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);//删除上传的XLS文件
                }

                Bind();
                Maticsoft.Common.MessageBox.Show(this, msg);
            }
            catch (Exception ex)
            {
                //LblMessage.Text = ex.Message;
            }

        }


       
        private string ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = "Sheet1$";
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                return AddDatasetToSQL(ds, 17);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private string AddDatasetToSQL(DataSet pds, int Cols)
        {
            string msg = "";
            sm = new Maticsoft.BLL.supplyPlan();
            Maticsoft.Model.supplyPlan s;
            Maticsoft.BLL.project pm = new Maticsoft.BLL.project();//项目
            Maticsoft.BLL.projectType ptm = new Maticsoft.BLL.projectType();//项目类型
            Maticsoft.Model.project p;
            dzs.BLL.supplierinfo slm = new BLL.supplierinfo();//供应商
            dzs.Model.supplierinfo sl;
            dzs.BLL.equipmentManage eqm = new BLL.equipmentManage();//设备管理单位
            dzs.BLL.user_project upm = new BLL.user_project();//物料员项目对应表

            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }

            int proidNullCount = 0;//记录项目id为空的记录
            int equipmentManageNullCount = 0;//记录设备管理单位为空的记录
            int equipmentManageNullNameCount = 0;//记录设备管理单位名称不存在于equipmentMange表的记录
            int insertCount = 0;//记录导入数据条数
    
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                s = new Maticsoft.Model.supplyPlan();
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {
                    #region 循环导入
                    DataRow r = pds.Tables[0].Rows[i];
                    if (!string.IsNullOrEmpty(r["项目编号"].ToString().Trim()))//必须要有项目编号
                    {
                        s.purchaseid = r["采购订单号"].ToString().Trim();
                        if (!string.IsNullOrEmpty(r["合同交货时间"].ToString().Trim()))
                        {
                            try
                            {
                                s.realSupplyDate = DateTime.Parse(r["合同交货时间"].ToString().Trim());
                            }
                            catch (Exception)
                            {

                            }
                        }

                        s.mbm = r["物料编码"].ToString().Trim();
                        s.material = r["物料描述"].ToString().Trim();

                        if (!string.IsNullOrEmpty(r["数量"].ToString().Trim()))
                        {
                            try
                            {
                                s.num = decimal.Parse(r["数量"].ToString().Trim());
                            }
                            catch (Exception)
                            {
                                s.num = 0;
                            }
                        }
                        s.unit = r["订单单位"].ToString().Trim();
                        if (!string.IsNullOrEmpty(r["订单含税单价"].ToString().Trim()))
                        {
                            try
                            {
                                s.unitPrice = decimal.Parse(r["订单含税单价"].ToString().Trim());
                            }
                            catch (Exception)
                            {
                                s.unitPrice = 0;
                            }
                        }
                        if (!string.IsNullOrEmpty(r["合同签订日期"].ToString().Trim()))
                        {
                            try
                            {
                                s.contractdate = DateTime.Parse(r["合同签订日期"].ToString().Trim());
                            }
                            catch (Exception)
                            {

                            }
                        }
                        
                        s.winBid = r["招标计划编号"].ToString().Trim();
                        //s.winBid = r["中标批次"].ToString().Trim();
                        //s.sourceProid = r["源项目编号"].ToString().Trim();
                        //s.sourceProjectName = r["源项目名称"].ToString().Trim();
                        s.sname = r["供应商名称"].ToString().Trim();
                        s.slink = r["联系人"].ToString().Trim();
                        s.slinkContent = r["联系方式"].ToString().Trim();
                        s.importTime = DateTime.Now;

                        if (!slm.ExistsByName(s.sname))//如果不存在此供应商则新建供应商
                        {
                            sl = new Model.supplierinfo();
                            sl.sname = s.sname;
                            sl.slink = s.slink;
                            sl.sphone = s.slinkContent;//联系方式放到了供应商联系电话里

                            s.sid = slm.Add(sl);
                        }
                        else
                        {
                            DataSet ds_supplier = slm.GetList(" sname='" + s.sname +"' ");

                           s.sid = Convert.ToInt32(ds_supplier.Tables[0].Rows[0]["sid"].ToString());//供应商ID，根据供应商名称和联系人来找
                        }

                        //if (!string.IsNullOrEmpty(r["实际供货期"].ToString().Trim()))
                        //{
                        //    try
                        //    {
                        //        s.realSupplyDate = DateTime.Parse(r["实际供货期"].ToString().Trim());
                        //    }
                        //    catch (Exception)
                        //    {

                        //    }
                        //}

                        //s.warrantyTime = r["质保日期"].ToString().Trim();
                        //s.coordinate = r["需要协调的问题"].ToString().Trim();
                        //s.deviceproductionstate = r["目前设备生产状态"].ToString().Trim();
                        s.remark = r["备注"].ToString().Trim();

                        if (!string.IsNullOrEmpty(s.sourceProid))//如果有源项目编号则供应计划类型为调配物资
                        {
                            s.supplyPlanType = "调配物资";
                        }
                        else
                        {
                            s.supplyPlanType = "正常";
                        }
                        s.technicalDataStatus = "未确认";//技术资料确认状态
                        s.supplyDateStatus = "未确认";//供货期确认状态
                        s.proid = r["项目编号"].ToString().Trim();

                        string projectTypeName = r["项目类型"].ToString().Trim();//项目类型名称
                        int projectTypeId = 0;//项目类型ID

                        if (!ptm.Exists(projectTypeName))//如果不存在此项目类型则新建项目类型
                        {
                            Maticsoft.Model.projectType pt = new Maticsoft.Model.projectType();
                            pt.projectTypeName = projectTypeName;

                            projectTypeId = ptm.Add(pt);
                        }
                        else
                        {
                            projectTypeId = Convert.ToInt32
                                (ptm.GetList(" projectTypeName='" + projectTypeName + "' ").Tables[0].Rows[0]["projectTypeId"]);
                        }

                        if (!pm.Exists(s.proid))//如果不存在此项目则新建项目
                        {
                            p = new Maticsoft.Model.project();
                            p.proid = s.proid;
                            p.projectName = r["项目名称"].ToString().Trim();
                            p.levelType = "新建";
                            //p.projectTypeId = int.Parse(this.ddl_projectType.SelectedValue);//项目类型
                            p.projectTypeId = projectTypeId;
                            if (Session["login"] != null)
                            {
                                DataSet ds = Session["login"] as DataSet;

                                int user_id = Convert.ToInt32(ds.Tables[0].Rows[0]["user_id"].ToString());

                                p.uid = user_id;//项目管理人
                            }

                            s.projectId = pm.Add(p);
                        }
                        else//如果存在此项目,则获取此项目的projectID
                        {
                            DataSet ds = pm.GetList(" proid='" + s.proid + "' ");

                            s.projectId = int.Parse(ds.Tables[0].Rows[0]["projectId"].ToString());
                        }

                        #region 设备管理单位验证
                        //s.equipmentManage = r["设备管理单位"].ToString().Trim();

                        //if (string.IsNullOrEmpty(s.equipmentManage))
                        //{
                        //    equipmentManageNullCount += 1;//记录设备管理单位为空的记录
                        //}
                        //else
                        //{
                        //   DataSet ds_equip = eqm.GetList(" equipmentManageName = '" + s.equipmentManage + "' ");                      
                        //    if (ds_equip.Tables[0].Rows.Count == 1)
                        //    {
                        //        sm.Add(s);//设备管理单位如果不为空且在设备管理单位表中有数据则插入数据
                        //        insertCount += 1; 
                        //    }
                        //    else
                        //    {
                        //        equipmentManageNullNameCount += 1;//记录设备管理单位名称不存在于表equipmentManage的记录
                        //    }

                        //}
                        #endregion

                        s.equipmentManage = r["设备管理单位"].ToString().Trim();
                        if (sm.Add(s) > 0)
                        {
                            insertCount += 1; 
                        }

                    }
                    else
                    {
                        proidNullCount += 1;//记录项目编号为空的记录条数 
                    }

                    #endregion

                }

                msg = "一共导入"+insertCount+"条数据。\\n";
                if (proidNullCount != 0)
                {
                    msg += "有"+proidNullCount+"条数据没有填写项目编号无法导入！\\n";
                }
                if (equipmentManageNullCount != 0)
                {
                    msg += "有" + equipmentManageNullCount + "条数据没有填写设备管理单位无法导入！\\n";
                }
                if (equipmentManageNullNameCount != 0)
                {
                    msg += "有" + equipmentManageNullNameCount + "条数据在设备管理单位记录表中不存在无法导入！\\n";
                }

            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return msg;
        }   
        private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if (inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    if (fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/upload/");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().ToString();
                    //modifyfilename = inputfile.PostedFile.FileName;
                    modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    if (!uploadfilepath.EndsWith("\\"))
                        orifilename = uploadfilepath + "\\" + modifyfilename;
                    else
                        orifilename = uploadfilepath + modifyfilename;
                    //如果存在,删除文件
                    if (File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("请选择要导入的Excel文件!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.GridView1.PageIndex = e.NewPageIndex;
            Bind();
            //BindDDL();
        }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "lbn_del")
            {
                int supplyPlanId = int.Parse(e.CommandArgument.ToString());

                if (new Maticsoft.BLL.supplyPlan().Delete(supplyPlanId))
                {
                    Bind();
                }
            }
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                LinkButton lbn_del = e.Row.FindControl("lbn_del") as LinkButton;

                Label lbl_supplyPlanId = e.Row.FindControl("lbl_supplyPlanId") as Label;
                Label lbl_gygly = e.Row.FindControl("lbl_gygly") as Label;
                

                DataSet ds = new Maticsoft.BLL.monthSupplayPlan().GetList(" supplyPlanId="+lbl_supplyPlanId.Text);

                if (ds.Tables[0].Rows.Count == 0)
                {
                    lbn_del.Enabled = true;
                }
                else
                {
                    lbn_del.Enabled = false;
                }

                if (!string.IsNullOrEmpty(lbl_gygly.Text))
                {
                    lbn_del.ForeColor = System.Drawing.Color.Red;
                }
                //else
                //{
                //    lbn_del.ForeColor = System.Drawing.Color.Black;
                //}
            }
        }
    //查询
        protected void btn_select_Click(object sender, EventArgs e)
        {
            Bind();
        }

       
    }
}